﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.Remoting.Messaging;
using System.Threading.Tasks;

namespace Sys.Excel
{
    #region [ delegate CellFormatter ]

    public delegate string CellFormatter(int i, int j, object row, object value);

    #endregion

    #region [ class CellStyle ]

    [Serializable]
    public sealed class CellStyle
    {
        private int m_code = 0;
        private string m_str = null;
        public static readonly CellStyle Body = new CellStyle(Color.Black, Color.White);
        public static readonly CellStyle Header = new CellStyle(Color.Black, Color.FromArgb(216, 216, 216), Align.Center, FontBoldWeight.Bold);

        public enum Align { Left, Center, Right }

        public enum FontBoldWeight { Normal, Bold }

        private bool Color_EQ(Color a, Color b)
        {
            return a.A == b.A && a.R == b.R && a.G == b.G && a.B == b.B;
        }

        private string Color_RGBA(Color color)
        {
            return string.Format("RGBA({0}, {1}, {2}, {3})", color.R, color.G, color.B, color.A);
        }

        public CellStyle()
            : this(Color.Black, Color.White, Align.Left, FontBoldWeight.Normal)
        {

        }

        public CellStyle(
            Color fg_color, Color bg_color,
            Align align = Align.Left, FontBoldWeight weight = FontBoldWeight.Normal)
        {
            this.FgColor = fg_color;
            this.BgColor = bg_color;
            this.TextAlign = align;
            this.FontWeight = weight;

            this.m_str = string.Format(
                "FgColor: {0}; BgColor: {1}; TextAlign: {2}, FontWeight: {3};",
                this.Color_RGBA(this.FgColor), this.Color_RGBA(this.BgColor), this.TextAlign, this.FontWeight);
            this.m_code = this.m_str.GetHashCode();
        }

        public Color FgColor { get; private set; }

        public Color BgColor { get; private set; }

        public Align TextAlign { get; private set; }

        public FontBoldWeight FontWeight { get; set; }

        public override bool Equals(object obj)
        {
            CellStyle style = obj as CellStyle;
            return style != null &&
                this.Color_EQ(this.FgColor, style.FgColor) &&
                this.Color_EQ(this.BgColor, style.BgColor) &&
                this.TextAlign == style.TextAlign &&
                this.FontWeight == style.FontWeight;
        }

        public override int GetHashCode() { return this.m_code; }

        public override string ToString() { return this.m_str; }
    }

    #endregion

    #region [ class Settings ]

    [Serializable]
    public sealed class Settings
    {
        private int m_maxlevel = 0;
        private IList<ColNode> m_leafNodes = null;
        internal static readonly IDictionary<string, CellFormatter> m_dictFormatters = new Dictionary<string, CellFormatter>();

        public Settings()
        {
            this.ColumnsAutoWidth = true;
        }

        internal Settings(Settings parent_Settings, int? pageIndex) : this()
        {
            this.ParentSettings = parent_Settings;
            this.PageIndex = pageIndex;
        }

        /// <summary>
        /// 注册 CellFormatter
        /// </summary>
        /// <param name="name"></param>
        /// <param name="formatter"></param>
        public static void RegCellFormatter(string name, CellFormatter formatter)
        {
            lock (Settings.m_dictFormatters)
            {
                Settings.m_dictFormatters[name] = formatter;
            }
        }

        /// <summary>
        /// GetLeafNodes
        /// </summary>
        /// <param name="p_nodes"></param>
        /// <returns></returns>
        private IList<ColNode> GetLeafNodes(IList<ColNode> p_nodes)
        {
            IList<ColNode> leafNodes = new List<ColNode>();
            if (p_nodes != null && p_nodes.Count > 0)
            {
                for (int i = 0; i < p_nodes.Count; i++)
                {
                    if (p_nodes[i] == null)
                        p_nodes[i] = new ColNode() { };

                    ColNode p_node = p_nodes[i];

                    if (p_node.ChildColNodes == null ||
                        p_node.ChildColNodes.Count == 0) { leafNodes.Add(p_node); continue; }

                    foreach (ColNode node in
                        p_node.ChildColNodes) { node.Level_Index = p_node.Level_Index + 1; }

                    foreach (ColNode node in
                        this.GetLeafNodes(p_node.ChildColNodes)) { leafNodes.Add(node); }
                }
            }
            return leafNodes;
        }

        /// <summary>
        /// 最大级数
        /// </summary>
        internal int MaxLevel
        {
            get
            {
                return this.m_maxlevel == 0 ?
                    this.LeafNodes.Max(node => node.Level_Index) : this.m_maxlevel;
            }
        }

        /// <summary>
        /// 所属页索引
        /// </summary>
        public int? PageIndex { get; private set; }

        /// <summary>
        /// 父级配置
        /// </summary>
        public Settings ParentSettings { get; private set; }

        /// <summary>
        /// 所有叶节点
        /// </summary>
        public IList<ColNode> LeafNodes
        {
            get
            {
                this.m_leafNodes =
                    this.m_leafNodes ?? this.GetLeafNodes(this.RootNodes);
                return this.m_leafNodes;
            }
        }

        /// <summary>
        /// 名称
        /// </summary>
        public string Name { get; set; }

        /// <summary>
        /// 每页数量(当 PageSize == 0 时不分页，默认为 0)
        /// </summary>
        public uint PageSize { get; set; }

        /// <summary>
        /// Excel 起始行
        /// </summary>
        public uint StartRowIndex { get; set; }

        /// <summary>
        /// Excel 起始列
        /// </summary>
        public uint StartColIndex { get; set; }

        /// <summary>
        /// 是否冻结表头
        /// </summary>
        public bool FreezeHeader { get; set; }

        /// <summary>
        /// 行高
        /// </summary>
        public uint RowHeight { get; set; }

        /// <summary>
        /// 列宽自动
        /// </summary>
        public bool ColumnsAutoWidth { get; set; }

        /// <summary>
        /// 表头单元格样式
        /// </summary>
        public CellStyle HeaderCellStyle { get; set; }

        /// <summary>
        /// 数据源
        /// </summary>
        public object DataSource { get; set; }

        /// <summary>
        /// 定义如何获取单元格数据
        /// </summary>
        public Func<CellValueContext, object> CellValueGetter { get; set; }

        /// <summary>
        /// 定义如何获取汇总行某列的数据
        /// </summary>
        public Func<TotalValueContext, object> TotalValueGetter { get; set; }

        /// <summary>
        /// 获取行样式
        /// </summary>
        public Func<int, object, CellStyle> RowStyleGetter { get; set; }

        /// <summary>
        /// 获取列样式
        /// </summary>
        public Func<int, ColNode, CellStyle> ColumnStyleGetter { get; set; }

        /// <summary>
        /// Excel 表创建后执行
        /// </summary>
        public Action<SheetBuildedContext> SheetBuilded { get; set; }

        /// <summary>
        /// 根级
        /// </summary>
        public IList<ColNode> RootNodes { get; set; }

        #region [ class CellValueContext ]

        public sealed class CellValueContext
        {
            internal CellValueContext(
                object row, int rowIndex, ColNode column, int columnIndex,
                Func<CellValueContext, object> getter)
            {
                this.Row = row;
                this.RowIndex = rowIndex;
                this.Column = column;
                this.ColumnIndex = columnIndex;
                this.DefaultGetter = getter;
            }

            public object Row { get; private set; }

            public int RowIndex { get; private set; }

            public ColNode Column { get; private set; }

            public int ColumnIndex { get; private set; }

            public Func<CellValueContext, object> DefaultGetter { get; private set; }
        }

        #endregion

        #region [ class TotalValueContext ]

        public sealed class TotalValueContext
        {
            internal TotalValueContext(
                Settings settings, int nodeIndex, ColNode node, IList<object> nodeValues)
            {
                this.Settings = settings;
                this.NodeIndex = nodeIndex;
                this.Node = node;
                this.NodeValues = nodeValues;
            }

            public Settings Settings { get; private set; }

            public int NodeIndex { get; private set; }

            public ColNode Node { get; private set; }

            public IList<object> NodeValues { get; private set; }
        }

        #endregion

        #region [ class SheetBuildedContext ]

        public sealed class SheetBuildedContext
        {
            private Action<int, int, object> m_action_setCellValue = null;
            private Action<int, int, CellStyle> m_action_setCellStyle = null;
            private Action<int, int, int, int> m_action_mergeCells = null;

            internal SheetBuildedContext(
                Settings settings,
                int headerRowCount,
                int bodyRowCount,
                int colCount,
                bool hasTotalRow,
                Action<int, int, object> action_setCellValue,
                Action<int, int, CellStyle> action_setCellStyle,
                Action<int, int, int, int> action_mergeCells)
            {
                this.Settings = settings;
                this.m_action_setCellValue = action_setCellValue;
                this.m_action_setCellStyle = action_setCellStyle;
                this.m_action_mergeCells = action_mergeCells;
                this.HeaderRowCount = headerRowCount;
                this.BodyRowCount = bodyRowCount;
                this.ColCount = colCount;
                this.HasTotalRow = hasTotalRow;
            }

            public Settings Settings { get; private set; }

            public int HeaderRowCount { get; private set; }

            public int BodyRowCount { get; private set; }

            public int ColCount { get; private set; }

            public bool HasTotalRow { get; private set; }

            public void SetCellValue(int rowIndex, int colIndex, object value)
            {
                if (this.m_action_setCellValue != null)
                    this.m_action_setCellValue(rowIndex, colIndex, value);
            }

            public void SetCellStyle(int rowIndex, int colIndex, CellStyle style)
            {
                if (this.m_action_setCellStyle != null)
                    this.m_action_setCellStyle(rowIndex, colIndex, style);
            }

            public void MergeCells(int startRow, int endRow, int startCol, int endCol)
            {
                if (this.m_action_mergeCells != null)
                    this.m_action_mergeCells.Invoke(startRow, endRow, startCol, endCol);
            }
        }

        #endregion
    }

    #endregion

    #region [ class ColNode ]

    [Serializable]
    public sealed class ColNode
    {
        private List<ColNode> m_leaf_nodes = null;
        private IList<ColNode> m_child_nodes = null;

        public ColNode()
        {
            this.Width = 30;
        }

        /// <summary>
        /// 节点所处的级别
        /// </summary>
        internal int Level_Index { get; set; }

        /// <summary>
        /// 叶节点
        /// </summary>
        internal IList<ColNode> LeafNodes
        {
            get
            {
                if (this.m_leaf_nodes == null)
                {
                    this.m_leaf_nodes = new List<ColNode>();
                    if (this.ChildColNodes != null && this.ChildColNodes.Count > 0)
                    {
                        foreach (ColNode node in this.ChildColNodes)
                        {
                            if (node.ChildColNodes == null || node.ChildColNodes.Count == 0) { this.m_leaf_nodes.Add(node); continue; }
                            this.m_leaf_nodes.AddRange(node.LeafNodes);
                        }
                    }
                    else { this.m_leaf_nodes.Add(this); }
                }
                return this.m_leaf_nodes;
            }
        }

        /// <summary>
        /// 字段
        /// </summary>
        public string Field { get; set; }

        /// <summary>
        /// 合并列中值相同的单元格用做对比的字段
        /// </summary>
        public string MergeField { get; set; }

        /// <summary>
        /// 标题
        /// </summary>
        public string Title { get; set; }

        /// <summary>
        /// 列宽
        /// </summary>
        public uint Width { get; set; }

        /// <summary>
        /// 格式化
        /// </summary>
        public CellFormatter Formatter { get; set; }

        /// <summary>
        /// 格式化器的名称
        /// </summary>
        public string FormatterName { get; set; }

        /// <summary>
        /// 子级
        /// </summary>
        public IList<ColNode> ChildColNodes
        {
            get { return this.m_child_nodes; }
            set { this.m_leaf_nodes = null; this.m_child_nodes = value; }
        }
    }

    #endregion

    #region [ class Dynamic ]

    public static class Dynamic
    {
        private static IDictionary<FieldInfo, Func<object, object>> m_f_getters = null;
        private static IDictionary<PropertyInfo, Func<object, object>> m_p_getters = null;

        static Dynamic()
        {
            Dynamic.m_f_getters = new Dictionary<FieldInfo, Func<object, object>>();
            Dynamic.m_p_getters = new Dictionary<PropertyInfo, Func<object, object>>();
        }

        public static Func<object, object> PropertyGetter(PropertyInfo property)
        {
            if (property == null)
                throw new ArgumentNullException("property");

            Func<object, object> getter = null;
            if (!Dynamic.m_p_getters.TryGetValue(property, out getter))
            {
                lock (Dynamic.m_p_getters)
                {
                    if (!Dynamic.m_p_getters.TryGetValue(property, out getter))
                    {
                        var parm = Expression.Parameter(typeof(object));
                        var exp_property = Expression.Property(Expression.Convert(parm, property.DeclaringType), property);
                        var lambda = Expression.Lambda<Func<object, object>>(Expression.Convert(exp_property, typeof(object)), parm);
                        Dynamic.m_p_getters[property] = getter = lambda.Compile();
                    }
                }
            }
            return getter;
        }

        public static Func<object, object> FieldGetter(FieldInfo field)
        {
            if (field == null)
                throw new ArgumentNullException("field");

            Func<object, object> getter = null;
            if (!Dynamic.m_f_getters.TryGetValue(field, out getter))
            {
                lock (Dynamic.m_p_getters)
                {
                    if (!Dynamic.m_f_getters.TryGetValue(field, out getter))
                    {
                        var parm = Expression.Parameter(typeof(object));
                        var exp_field = Expression.Field(Expression.Convert(parm, field.DeclaringType), field);
                        var lambda = Expression.Lambda<Func<object, object>>(Expression.Convert(exp_field, typeof(object)), parm);
                        Dynamic.m_f_getters[field] = getter = lambda.Compile();
                    }
                }
            }
            return getter;
        }
    }

    #endregion

    #region [ interface IDataSource ]

    public interface IDataSource
    {
        IEnumerable Get(Settings settings);
    }

    #endregion

    #region [ interface IExcelExporter ]

    public interface IExcelExporter
    {
        Task BuildSheetsAsync(
            Stream stream, IEnumerable<Settings> settings_list, bool useOldVersion = false,
            object hostContext = null);
    }

    #endregion

    #region [ class IExcelExporter_Extensions ]

    public static class IExcelExporter_Extensions
    {
        public static Task BuildSheetsAsync(this IExcelExporter exporter, Stream stream, bool useOldVersion, params Settings[] settings_array)
        {
            return exporter.BuildSheetsAsync(stream, settings_array, useOldVersion);
        }

        public static Task BuildSheetsAsync_2003(this IExcelExporter exporter, Stream stream, params Settings[] settings_array)
        {
            return exporter.BuildSheetsAsync(stream, settings_array, true);
        }

        public static Task BuildSheetsAsync_2007(this IExcelExporter exporter, Stream stream, params Settings[] settings_array)
        {
            return exporter.BuildSheetsAsync(stream, settings_array);
        }
    }

    #endregion

    #region [ class ExcelExporterBase<TWorkBook, TWorkSheet, TCellStyle> ]

    public abstract class ExcelExporterBase<TWorkBook, TWorkSheet, TCellStyle> : IExcelExporter
        where TWorkBook : class
        where TWorkSheet : class
        where TCellStyle : class
    {
        #region [ private Private_GetCellStyle ]

        private TCellStyle Private_GetCellStyle(
            TWorkBook workbook, IDictionary<CellStyle, TCellStyle> stylesDict,
            TCellStyle style, CellStyle cellStyle)
        {
            TCellStyle t_style = null;
            if (!stylesDict.TryGetValue(cellStyle, out t_style))
            {
                stylesDict[cellStyle] =
                    t_style = this.CreateCellStyle(workbook, style, cellStyle);
            }
            return t_style;
        }

        #endregion

        #region [ private Default_ValueGetter ]

        private object Default_ValueGetter(Settings.CellValueContext ctx)
        {
            var obj = ctx.Row;
            var field = ctx.Column.Field;

            if (obj != null && field != null)
            {
                if (obj is DataRow)
                {
                    DataRow row = obj as DataRow;
                    if (row.Table.Columns.Contains(field)) return row[field];
                }
                else if (obj is IDictionary)
                {
                    IDictionary dict = obj as IDictionary;
                    if (dict.Contains(field)) return dict[field];
                }
                else if (obj is IDictionary<string, object>)
                {
                    object val = null;
                    IDictionary<string, object> dict_g = obj as IDictionary<string, object>;
                    if (dict_g.TryGetValue(field, out val)) return val;
                }
                else
                {
                    Type obj_type = obj.GetType();
                    PropertyInfo pro = obj_type.GetProperty(field, BindingFlags.Public | BindingFlags.Instance);
                    if (pro != null) return Dynamic.PropertyGetter(pro)(obj);
                    FieldInfo fie = obj_type.GetField(field, BindingFlags.Public | BindingFlags.Instance);
                    if (fie != null) return Dynamic.FieldGetter(fie)(obj);
                }
            }
            return null;
        }

        #endregion

        #region [ private Get_DataSource ]

        private IEnumerable<object> Get_DataSource(Settings settings)
        {
            IEnumerable<object> data = null;
            object dataSource = settings.DataSource;

            if (dataSource != null)
            {
                Type type = dataSource.GetType();
                if (type.IsGenericType)
                {
                    Type g_type = type.GetGenericTypeDefinition();
                    if (g_type == typeof(Func<>))
                    {
                        dataSource = ((Delegate)dataSource).DynamicInvoke();
                    }
                    else if (g_type == typeof(Lazy<>))
                    {
                        dataSource = Dynamic.PropertyGetter(type.GetProperty("Value"))(dataSource);
                    }
                }

                if (dataSource is IDataSource)
                {
                    dataSource = ((IDataSource)dataSource).Get(settings);
                }
                else if (dataSource is DataSet)
                {
                    DataSet dataSet = dataSource as DataSet;
                    dataSource = dataSet.Tables.Count > 0 ? dataSet.Tables[0] : null;
                }

                if (dataSource is DataTable)
                {
                    data = (dataSource as DataTable).AsEnumerable().Cast<object>();
                }
                else if (dataSource is ICollection)
                {
                    data = (dataSource as ICollection).Cast<object>();
                }
                else if (dataSource is IEnumerable)
                {
                    data = (dataSource as IEnumerable).Cast<object>().ToList();
                }
            }

            return data;
        }

        #endregion

        #region [ private ToPagerSettings ]

        private IEnumerable<Settings> ToPagerSettings(Settings settings)
        {
            if (settings != null)
            {
                if (settings.DataSource == null ||
                    settings.PageSize == 0) yield return settings;
                else
                {
                    int index = 0;
                    var dataSource = this.Get_DataSource(settings);
                    if (dataSource == null) yield return settings;
                    else
                    {
                        while (true)
                        {
                            var pager = dataSource.Skip(
                                index * (int)settings.PageSize).Take((int)settings.PageSize);
                            if (!pager.Any()) { yield break; }
                            yield return new Settings(settings, index)
                            {
                                SheetBuilded = settings.SheetBuilded,
                                CellValueGetter = settings.CellValueGetter,
                                TotalValueGetter = settings.TotalValueGetter,
                                ColumnStyleGetter = settings.ColumnStyleGetter,
                                ColumnsAutoWidth = settings.ColumnsAutoWidth,
                                DataSource = pager,
                                FreezeHeader = settings.FreezeHeader,
                                HeaderCellStyle = settings.HeaderCellStyle,
                                Name = string.Format("{0}({1})", settings.Name, index + 1),
                                PageSize = 0,
                                RowHeight = settings.RowHeight,
                                RootNodes = settings.RootNodes,
                                RowStyleGetter = settings.RowStyleGetter,
                                StartColIndex = settings.StartColIndex,
                                StartRowIndex = settings.StartRowIndex
                            };
                            index++;
                        }
                    }
                }
            }
        }

        #endregion

        #region [ private BuildHeader ]

        private void BuildHeader(
            TWorkBook workbook, TWorkSheet sheet,
            Settings settings, IList<ColNode> p_nodes, IDictionary<CellStyle, TCellStyle> stylesDict,
            int startRowIndex = 0, int startColIndex = 0)
        {
            if (p_nodes != null && p_nodes.Count > 0)
            {
                for (int i = 0; i < p_nodes.Count; i++)
                {
                    ColNode p_node = p_nodes[i];
                    int col_index = startColIndex;
                    int row_index = startRowIndex + p_node.Level_Index;
                    bool is_leaf = p_node.ChildColNodes == null || p_node.ChildColNodes.Count == 0;

                    this.SetCell(
                        workbook, sheet,
                        row_index, col_index, settings, p_node, p_node.Title,
                        default(TCellStyle), true, false);

                    if (!is_leaf)
                    {
                        this.BuildHeader(
                            workbook, sheet, settings, p_node.ChildColNodes,
                            stylesDict, startRowIndex, col_index);
                    }

                    startColIndex += p_node.LeafNodes.Count;

                    this.MergeCells(
                        workbook, sheet, row_index,
                        is_leaf ? startRowIndex + settings.MaxLevel : row_index, col_index,
                        startColIndex - 1, settings);
                }
            }
        }

        #endregion

        #region [ private SetHeaderStyle ]

        private void SetHeaderStyle(
            TWorkBook workbook, TWorkSheet sheet,
            Settings settings, IList<ColNode> p_nodes, IDictionary<CellStyle, TCellStyle> stylesDict,
            int startRowIndex, int startColIndex)
        {
            var style = this.Private_GetCellStyle(
                workbook, stylesDict, null, settings.HeaderCellStyle ?? CellStyle.Header);

            for (int i = 0; i < settings.MaxLevel + 1; i++)
            {
                for (int j = 0; j < settings.LeafNodes.Count; j++)
                {
                    this.SetCell(
                        workbook, sheet,
                        startRowIndex + i, startColIndex + j, settings, null, null,
                        style, false);
                }
            }
        }

        #endregion

        #region [ private BuildBody ]

        private void BuildBody(
            TWorkBook workbook, TWorkSheet sheet,
            Settings settings, Tuple<IEnumerable<object>, Func<Settings.CellValueContext, object>> tuple,
            IDictionary<CellStyle, TCellStyle> stylesDict, int startRowIndex, int startColIndex,
            out IDictionary<ColNode, IList<object>> colsValues,
            out int total)
        {
            int i = 0;
            colsValues = new Dictionary<ColNode, IList<object>>();
            if (tuple != null)
            {
                IEnumerable<object> dataList = tuple.Item1;
                if (dataList != null)
                {
                    CellFormatter[] formatter_Array =
                        settings.LeafNodes.Select(node =>
                        {
                            if (node == null) return null;
                            CellFormatter formatter = node.Formatter;
                            if (formatter == null && node.FormatterName != null
                                ) { Settings.m_dictFormatters.TryGetValue(node.FormatterName, out formatter); }
                            return formatter;
                        }).ToArray();

                    CellStyle[] columns_Styles = settings.LeafNodes.Select(
                        (node, j) => settings.ColumnStyleGetter == null ? null : settings.ColumnStyleGetter(j, node)).ToArray();

                    foreach (object obj in dataList)
                    {
                        CellStyle row_style =
                            settings.RowStyleGetter == null ?
                                null : settings.RowStyleGetter.Invoke(i, obj);

                        for (int j = 0, n_len = settings.LeafNodes.Count; j < n_len; j++)
                        {
                            var node = settings.LeafNodes[j];
                            if (node != null)
                            {
                                IList<object> vals = null;
                                var formatter = formatter_Array[j];
                                var cell_val = tuple.Item2.Invoke(new Settings.CellValueContext(obj, i, node, j, this.Default_ValueGetter));
                                var style = this.GetCellStyle(workbook, sheet, settings, startRowIndex + i, startColIndex + j);

                                if (!colsValues.TryGetValue(node, out vals))
                                {
                                    colsValues[node] = vals = new List<object>();
                                }

                                vals.Add(cell_val);

                                this.SetCell(workbook, sheet,
                                    startRowIndex + i, startColIndex + j, settings,
                                    node, formatter == null ? cell_val : formatter.Invoke(i, j, obj, cell_val),
                                    this.Private_GetCellStyle(workbook, stylesDict, style, row_style ?? columns_Styles[j] ?? CellStyle.Body),
                                    cell_val != null);
                            }
                        }
                        i++;
                    }
                }
            }
            total = i;
        }

        #endregion

        #region [ private MergeBodyCells ]

        private void MergeBodyCells(
            TWorkBook workbook, TWorkSheet sheet,
            Settings settings, IDictionary<ColNode, IList<object>> colsValues,
            int startRowIndex, int startColIndex)
        {
            if (colsValues != null)
            {
                for (int i = 0, n_len = settings.LeafNodes.Count; i < n_len; i++)
                {
                    ColNode node = settings.LeafNodes[i];
                    if (node != null && !string.IsNullOrWhiteSpace(node.MergeField))
                    {
                        IList<object> values = null;
                        if (colsValues.TryGetValue(node, out values))
                        {
                            int count = 0;
                            int first_i = 0;
                            string first_val = null;
                            var listStrs = values.Select(obj => Convert.ToString(obj)).ToList();
                            int list_count = listStrs.Count;
                            for (int j = 0; j < list_count + 1; j++)
                            {
                                string str_j = null;
                                if (j == list_count || (str_j = listStrs[j]) != first_val)
                                {
                                    if (count > 0)
                                    {
                                        this.MergeCells(
                                            workbook, sheet,
                                            startRowIndex + first_i, startRowIndex + first_i + count,
                                            startColIndex + i, startColIndex + i, settings);
                                    }
                                    count = 0; first_i = j; first_val = str_j;
                                }
                                else { count++; }
                            }
                        }
                    }
                }
            }
        }

        #endregion

        #region [ private AddTotalRow ]

        private void AddTotalRow(
            TWorkBook workbook, TWorkSheet sheet,
            Settings settings, IDictionary<ColNode, IList<object>> colsValues,
            IDictionary<CellStyle, TCellStyle> stylesDict,
            int startRowIndex, int startColIndex)
        {
            if (colsValues != null)
            {
                for (int j = 0, n_len = settings.LeafNodes.Count; j < n_len; j++)
                {
                    var node = settings.LeafNodes[j];
                    if (node != null)
                    {
                        IList<object> colDatas = null;
                        if (colsValues.TryGetValue(node, out colDatas))
                        {
                            var totalValue = settings.TotalValueGetter.Invoke(new Settings.TotalValueContext(settings, j, node, colDatas));
                            TCellStyle style = this.GetCellStyle(workbook, sheet, settings, startRowIndex, startColIndex + j);

                            this.SetCell(workbook, sheet,
                                startRowIndex, startColIndex + j, settings,
                                node, totalValue,
                                this.Private_GetCellStyle(workbook, stylesDict, style, CellStyle.Header),
                                totalValue != null);
                        }
                    }
                }
            }
        }

        #endregion

        #region [ private SheetBuilded ]

        private void SheetBuilded(
            TWorkBook workbook, TWorkSheet sheet,
            Settings settings, IDictionary<CellStyle, TCellStyle> stylesDict,
            int bodyRowCount, int colCount)
        {
            int start_col = (int)settings.StartColIndex;

            settings.SheetBuilded.Invoke(
                new Settings.SheetBuildedContext(
                    settings, settings.MaxLevel, bodyRowCount, colCount,
                    settings.TotalValueGetter != null,
                    (rowIndex, colIndex, value) =>
                    {
                        var index = colIndex - start_col;
                        this.SetCell(
                            workbook, sheet,
                            rowIndex, colIndex, settings,
                            index < 0 ? null : settings.LeafNodes[index], value, null, true, false);
                    },
                    (rowIndex, colIndex, style) =>
                    {
                        var index = colIndex - start_col;
                        TCellStyle t_style = this.GetCellStyle(workbook, sheet, settings, rowIndex, colIndex);
                        this.SetCell(
                            workbook, sheet,
                            rowIndex, colIndex, settings,
                            index < 0 ? null : settings.LeafNodes[index], null,
                            this.Private_GetCellStyle(workbook, stylesDict, t_style, style), false, true);
                    },
                    (startRow, endRow, startCol, endCol) =>
                    {
                        this.MergeCells(
                            workbook, sheet,
                            startRow, endRow, startCol, endCol, settings);
                    }));
        }

        #endregion

        #region [ protected ]

        protected abstract TWorkBook CreateWorkBook(bool useOldVersion);

        protected abstract TWorkSheet CreateWorkSheet(TWorkBook workbook, string name, Settings settings);

        protected abstract TCellStyle CreateCellStyle(TWorkBook workbook, TCellStyle style, CellStyle cellStyle);

        protected abstract TCellStyle GetCellStyle(TWorkBook workbook, TWorkSheet sheet, Settings settings, int row, int col);

        protected abstract void SetDefaultRowHeight(TWorkBook workbook, TWorkSheet sheet, uint height);

        protected abstract void SetCell(TWorkBook workbook, TWorkSheet sheet, int row, int col, Settings settings, ColNode node, object value, TCellStyle style, bool setValue = true, bool setStyle = true);

        protected abstract void MergeCells(TWorkBook workbook, TWorkSheet sheet, int startRow, int endRow, int startCol, int endCol, Settings settings);

        protected abstract void FreezePane(TWorkBook workbook, TWorkSheet sheet, int rowSplit, int colSplit);

        protected abstract void SetColumnsWidth(TWorkBook workbook, TWorkSheet sheet, Settings settings, uint[] widthArray, int startCol);

        protected abstract void AutoFillColumns(TWorkBook workbook, TWorkSheet sheet, Settings settings, int startCol);

        protected abstract void Save(TWorkBook workbook, bool useOldVersion, Stream stream);

        #endregion

        #region [ IExcelExporter ]

        public Task BuildSheetsAsync(
            Stream stream, IEnumerable<Settings> settings_list, bool useOldVersion = false,
            object hostContext = null)
        {
            if (stream == null)
                throw new ArgumentNullException("stream");

            if (settings_list == null)
                throw new ArgumentNullException("settings_list");

            hostContext = hostContext ?? CallContext.HostContext;

            return Task.Factory.StartNew(() =>
            {
                TWorkBook workbook = this.CreateWorkBook(useOldVersion);
                IDictionary<CellStyle, TCellStyle> stylesDict = new Dictionary<CellStyle, TCellStyle>();

                if (CallContext.HostContext == null) { CallContext.HostContext = hostContext; }

                foreach (Settings settings in
                    settings_list.SelectMany(set => this.ToPagerSettings(set)))
                {
                    if (settings != null)
                    {
                        int total = 0;
                        int len = settings.LeafNodes.Count;
                        int start_row = (int)settings.StartRowIndex;
                        int start_col = (int)settings.StartColIndex;
                        var dataSource = this.Get_DataSource(settings);
                        var valueGetter = settings.CellValueGetter ?? this.Default_ValueGetter;
                        IDictionary<ColNode, IList<object>> colsValues = null;
                        TWorkSheet sheet = this.CreateWorkSheet(workbook,
                            string.IsNullOrWhiteSpace(settings.Name) ? null : settings.Name, settings);

                        this.SetDefaultRowHeight(workbook, sheet, settings.RowHeight);

                        this.BuildHeader(
                            workbook, sheet, settings,
                            settings.RootNodes, stylesDict, start_row, start_col);

                        this.SetHeaderStyle(
                            workbook, sheet, settings,
                            settings.RootNodes, stylesDict, start_row, start_col);

                        this.BuildBody(
                            workbook, sheet, settings,
                            Tuple.Create(dataSource, valueGetter),
                            stylesDict, start_row + settings.MaxLevel + 1, start_col,
                            out colsValues, out total);

                        this.MergeBodyCells(
                            workbook, sheet, settings,
                            colsValues, start_row + settings.MaxLevel + 1, start_col);

                        if (settings.TotalValueGetter != null)
                        {
                            this.AddTotalRow(
                                workbook, sheet, settings, colsValues,
                                stylesDict, start_row + settings.MaxLevel + 1 + total,
                                start_col);
                        }

                        if (settings.SheetBuilded != null)
                        {
                            this.SheetBuilded(
                                workbook, sheet, settings, stylesDict, total, len);
                        }

                        if (settings.FreezeHeader)
                        {
                            this.FreezePane(
                                workbook, sheet, start_row + settings.MaxLevel + 1,
                                start_col);
                        }

                        if (settings.ColumnsAutoWidth)
                        {
                            this.AutoFillColumns(
                                workbook, sheet, settings, start_col);
                        }
                        else
                        {
                            this.SetColumnsWidth(
                                workbook, sheet, settings,
                                settings.LeafNodes.Select(col => col.Width).ToArray(),
                                start_col);
                        }

                        if (colsValues != null) colsValues.Clear();
                    }
                }

                stylesDict.Clear();

                using (stream) { this.Save(workbook, useOldVersion, stream); }
            });
        }

        #endregion
    }

    #endregion
}